# coding=utf-8
import threading
import time
import os
import threadpool
import re
import psycopg2
import datetime
from  db_tables import *
from socket import *
import csv

GW_PARTITION = 'P1099'

database="matrix_ningjian"
user="postgres"
password="postgres"
host="172.16.8.91"
port="5432"


# GW_PARTITION = 'P1101'
#
# database="matrixconnect"
# user="postgres"
# password="POSTGRES99"
# host="172.16.6.90"
# port="5432"


database_data="matrixdata"
user_data="postgres"
password_data="POSTGRES99"
host_data="172.16.6.91"
port_data="5432"


vins_file = "vins_org.csv"

conn_evplatform = psycopg2.connect(database=database, user=user, password=password, host=host, port=port)
cur_evplatform = conn_evplatform.cursor()

conn_evdb = psycopg2.connect(database=database_data, user=user_data, password=password_data, host=host_data, port=port_data)
cur_evdb = conn_evdb.cursor()


def write_data_db(vin,iccid):
    """
    根据登录信息写入数据库
    """
    #加入车辆信息
    cur_evplatform.execute("SELECT vin_17 FROM vehicles where vin_17='%s';" % vin)
    rows = cur_evplatform.fetchall()        # all rows in table
    if not rows:
        try:
            cur_evplatform.execute("""INSERT INTO public.vehicles(
                vin_17,vin, partition,
                protocol_id, is_registered,
                create_date,
                deleted, lock_status, total_mileage, iccid,secret_key_id,terminal_id)
                VALUES ('%s','%s', '%s', 2, 0, '%s', 0, 0, 0, '%s',1,'18591950512');""" % (vin,vin ,GW_PARTITION, datetime.datetime.now(), iccid))
            conn_evplatform.commit()
        except Exception as e:
            print(e)
    else:
        try:
            cur_evplatform.execute("""UPDATE public.vehicles SET
                vin = '%s', partition = '%s',iccid = '%s', terminal_id = '18591950512' 
                WHERE vin_17='%s';""" % (vin,GW_PARTITION,iccid,vin))
            conn_evplatform.commit()
        except Exception as e:
            print(e)

    # 加入数据记录表
    # tables_sql = [sql_alarm, sql_bulkcapacitortemperature, sql_bulkcapacitorvoltage,sql_drivemotor,sql_extremum,sql_fuelcell,sql_gps,sql_motor,sql_vehicle]
    # vin_lower = vin.lower()
    # try:
    #     for sql in tables_sql:
    #         cur_evdb.execute(sql % (vin_lower,vin_lower))
    #         conn_evdb.commit()
    # except Exception as e:
    #     print(e)

def drop_data_db(vin,iccid):
    vin_lower = vin.lower()
    try:
        cur_evdb.execute("TRUNCATE table alarm_%s" % vin_lower)
        conn_evdb.commit()
        cur_evdb.execute("TRUNCATE table bulkcapacitortemperature_%s" % vin_lower)
        conn_evdb.commit()
        cur_evdb.execute("TRUNCATE table bulkcapacitorvoltage_%s" % vin_lower)
        conn_evdb.commit()
        cur_evdb.execute("TRUNCATE table drivemotor_%s" % vin_lower)
        conn_evdb.commit()
        cur_evdb.execute("TRUNCATE table extremum_%s" % vin_lower)
        conn_evdb.commit()
        cur_evdb.execute("TRUNCATE table fuelcell_%s" % vin_lower)
        conn_evdb.commit()
        cur_evdb.execute("TRUNCATE table gps_%s" % vin_lower)
        conn_evdb.commit()
        cur_evdb.execute("TRUNCATE table motor_%s" % vin_lower)
        conn_evdb.commit()
        cur_evdb.execute("TRUNCATE table vehicle_%s" % vin_lower)
        conn_evdb.commit()
    except Exception as e:
        print(e)

def register():
    csvfile = csv.reader(open(vins_file, 'r'))
    for line in csvfile:
        vin = line[0]
        iccid = vin+"000"
        #drop_data_db(vin, iccid)
        write_data_db(vin,iccid)
        print(vin)

if __name__ == '__main__':
    register()
    print("OK")


